package com.totoro.service;

import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.totoro.domain.ReviewProject;


/**
 * 
 * @author xiang fusehng
 * 
 * 导出Excel表单；
 *
 */
public class ReviewExcelService extends AbstractExcelView {
	
	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		ReviewProject reviewProject = (ReviewProject) model.get("reviewProject");
		String formattedDate = (String) model.get("formattedDate");
		//响应报文头的处理：
		response.setHeader("Content-Disposition", 
				"inline;filename="+new String(
						"　山东建筑大学毕业设计（论文）评阅人评审表.xls".getBytes(), 
						"iso8859-1"));
		
		
		 // 创建单元格样式  
		HSSFCellStyle dateToRightCellStyle = workbook.createCellStyle();
	    HSSFCellStyle scheduleCellStyle = workbook.createCellStyle();
	    HSSFCellStyle titleCellStyle = workbook.createCellStyle();
	    HSSFCellStyle underlineCellStyle = workbook.createCellStyle();
	    HSSFCellStyle cotentTitleCellStyle = workbook.createCellStyle();
	    HSSFCellStyle cotentTitleDownCellStyle = workbook.createCellStyle();
	    HSSFCellStyle resultsCellStyle = workbook.createCellStyle();
	    HSSFCellStyle rlLineCCellStyle = workbook.createCellStyle();
	    HSSFCellStyle upLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle upRLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle upLLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle downRLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle downLLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle rightLLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle leftLLineLCellStyle = workbook.createCellStyle();
	    HSSFCellStyle centerCellStyle = workbook.createCellStyle();
	    // 指定单元格居中对齐  
	    centerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    upRLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    leftLLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    rightLLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    upLLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    downRLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    downLLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    dateToRightCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	    scheduleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	    titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
	    underlineCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    cotentTitleDownCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	    cotentTitleCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	    resultsCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    rlLineCCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    upLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    upRLineLCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    // 指定单元格垂直居中对齐  
	    dateToRightCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    centerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    scheduleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
	    titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    resultsCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    rlLineCCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    upLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	   
	    upRLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    upRLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    leftLLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    rightLLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    upLLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    downRLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    downLLineLCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
	    // 指定当单元格内容显示不下时自动换行  
	    scheduleCellStyle.setWrapText(true); 
	    titleCellStyle.setWrapText(true);
	    underlineCellStyle.setWrapText(true);
	    cotentTitleCellStyle.setWrapText(true);
	    resultsCellStyle.setWrapText(true);
	    upLineLCellStyle.setWrapText(true);
	    upRLineLCellStyle.setWrapText(true);
	    dateToRightCellStyle.setWrapText(true);
	    leftLLineLCellStyle.setWrapText(true);
	    rightLLineLCellStyle.setWrapText(true);
	    upLLineLCellStyle.setWrapText(true);
	    downRLineLCellStyle.setWrapText(true);
	    downLLineLCellStyle.setWrapText(true);
	    // 设置单元格字体  
	    HSSFFont font = workbook.createFont();   
	    font.setFontName("宋体");  
	    font.setFontHeight((short) 220); 
	    scheduleCellStyle.setFont(font); 
	    leftLLineLCellStyle.setFont(font);
	    rightLLineLCellStyle.setFont(font);
	    upLLineLCellStyle.setFont(font);
	    downRLineLCellStyle.setFont(font);
	    downLLineLCellStyle.setFont(font);
	    centerCellStyle.setFont(font);
	    cotentTitleDownCellStyle.setFont(font);
	    dateToRightCellStyle.setFont(font);
	    
	    HSSFFont titleFont = workbook.createFont(); 
	    titleFont.setFontName("宋体");
	    titleFont.setFontHeight((short)250);
	    titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	   
	    titleCellStyle.setFont(titleFont);
	    cotentTitleCellStyle.setFont(font);
	    upLineLCellStyle.setFont(font);
	    upRLineLCellStyle.setFont(font);
	    //设置单元格边框
	    underlineCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    //cotentTitleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    resultsCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    resultsCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    resultsCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    resultsCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    rlLineCCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    rlLineCCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    upRLineLCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    upRLineLCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    upLineLCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    cotentTitleDownCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    
	    leftLLineLCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    rightLLineLCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    upLLineLCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    upLLineLCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    downRLineLCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    downRLineLCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    downLLineLCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    downLLineLCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    
		//在Excel工作簿中建一工作表，工作表名
		HSSFSheet sheet = workbook.createSheet("山东建筑大学毕业设计（论文）评阅人评审表");

		//Cell的宽度,正常的宽度1800左右；
		//this.setColumns(sheet, 1800);
		
		int rowNum = 0;

			//显示附表8字样
			HSSFRow schedule = sheet.createRow(0);
			Cell scheduleCell = schedule.createCell(0);
			scheduleCell.setCellValue("附表8:");
			scheduleCell.setCellStyle(scheduleCellStyle);
			//在sheet中创建一个新行（表格题目） 
			HSSFRow title = sheet.createRow(1);		
			//合并单元格：将1行0列至1行8列的单元格合并为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));
			//创建题目行中，第一个，也是惟一单元格
			Cell cell = title.createCell(0);
			//为题目单元格赋值
			cell.setCellValue("山东建筑大学毕业设计（论文）评阅人评审表");	
			cell.setCellStyle(titleCellStyle);
			
			//创建论文题目
			HSSFRow title2 = sheet.createRow(2);
			Cell titleCell = title2.createCell(0);
			
			titleCell.setCellValue("题目:");
			titleCell.setCellStyle(cotentTitleCellStyle);
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 4));
			//论文题目的内容
			
			Cell titleContent = title2.createCell(1);
			titleContent.setCellValue(reviewProject.getProject().getTitle());
			Cell titleContent1 = title2.createCell(2);
			Cell titleContent2 = title2.createCell(3);
			Cell titleContent3 = title2.createCell(4);
			
			titleContent.setCellStyle(underlineCellStyle);
			titleContent1.setCellStyle(underlineCellStyle);
			titleContent2.setCellStyle(underlineCellStyle);
			titleContent3.setCellStyle(underlineCellStyle);
			
			HSSFRow informationRow = sheet.createRow(3);
			Cell classCell = informationRow.createCell(0);
			classCell.setCellValue("班级:");
			classCell.setCellStyle(cotentTitleCellStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 2));
			Cell classCell2 = informationRow.createCell(1);
			classCell2.setCellValue(reviewProject.getProject().getStudent().getStudentClass().getDescription());
			classCell2.setCellStyle(underlineCellStyle);
			Cell classCell3 = informationRow.createCell(2);
			classCell3.setCellStyle(underlineCellStyle);
			
			Cell nameCell = informationRow.createCell(3);
			nameCell.setCellValue("姓名:");
			nameCell.setCellStyle(underlineCellStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 5));
			
			Cell nameCell2 = informationRow.createCell(4);
			nameCell2.setCellValue(reviewProject.getProject().getStudent().getName());
			nameCell2.setCellStyle(underlineCellStyle);
			Cell nameCell3 = informationRow.createCell(5);
			nameCell3.setCellStyle(underlineCellStyle);
			
			Cell stuno = informationRow.createCell(6);
			stuno.setCellValue("学号:");
			stuno.setCellStyle(cotentTitleDownCellStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 8));
			Cell stunoCell = informationRow.createCell(7);
			stunoCell.setCellValue(reviewProject.getProject().getStudent().getNo());
			stunoCell.setCellStyle(underlineCellStyle);
			Cell stunoCell1 = informationRow.createCell(8);
			stunoCell1.setCellStyle(underlineCellStyle);
			
			Cell stunoCell2 = informationRow.createCell(9);
			stunoCell2.setCellStyle(underlineCellStyle);

			//得分情况
			
			HSSFRow resultRow = sheet.createRow(4);
			sheet.addMergedRegion(new CellRangeAddress(4, 6, 0,2));
			Cell qualityCell = resultRow.createCell(0);
			qualityCell.setCellStyle(resultsCellStyle);
			qualityCell.setCellValue("设计（论文）质量评分（正确性、条理性、规范性、合理性、清晰、工作量）（0—10分）");
			
			sheet.addMergedRegion(new CellRangeAddress(4, 6, 0,2));
			Cell qualityScoreCell = resultRow.createCell(3);
			sheet.addMergedRegion(new CellRangeAddress(4, 6, 3,3));
			qualityScoreCell.setCellValue(reviewProject.getDocQualityScore());
			qualityScoreCell.setCellStyle(rlLineCCellStyle);
			HSSFRow resultRow6 = sheet.createRow(5);
			Cell quality1 = resultRow6.createCell(3);
			quality1.setCellStyle(rlLineCCellStyle);
			
			Cell qualitya = resultRow6.createCell(0);
			qualitya.setCellStyle(leftLLineLCellStyle);
			
			
			
			HSSFRow resultRow7 = sheet.createRow(6);
			Cell quality2 = resultRow7.createCell(3);
			quality2.setCellStyle(rlLineCCellStyle);
			
			Cell qualityb = resultRow7.createCell(0);
			qualityb.setCellStyle(leftLLineLCellStyle);
			
			HSSFRow resultRow8 = sheet.createRow(7);
			Cell quality3 = resultRow8.createCell(3);
			quality3.setCellStyle(rlLineCCellStyle);
			
			
			sheet.addMergedRegion(new CellRangeAddress(4, 6,4,6));
			Cell technologyCell = resultRow.createCell(4);
			 technologyCell.setCellStyle(resultsCellStyle);
			 technologyCell.setCellValue("成果的技术水平（实用性和创新性）（0—10分）");
			 
			 sheet.addMergedRegion(new CellRangeAddress(4, 6,7,7));
			 Cell tecScroeCell = resultRow.createCell(7);
			 tecScroeCell.setCellStyle(rlLineCCellStyle);
			 tecScroeCell.setCellValue(reviewProject.getAchievementLevel());
			 Cell tecScore1 = resultRow6.createCell(7);
			 tecScore1.setCellStyle(rlLineCCellStyle);
			 Cell tecScore2 = resultRow7.createCell(7);
			 tecScore2.setCellStyle(rlLineCCellStyle);
			 
			 sheet.addMergedRegion(new CellRangeAddress(4, 6,8,8));
			 Cell resultCell = resultRow.createCell(8);
			 resultCell.setCellStyle(centerCellStyle);
			 resultCell.setCellValue("总分");
			
			 sheet.addMergedRegion(new CellRangeAddress(4, 6,9,9));
			 Cell retScroeCell = resultRow.createCell(9);
			 retScroeCell.setCellValue(reviewProject.getProject().getScore());
			 retScroeCell.setCellStyle(rlLineCCellStyle);
			 
			 Cell retScroeCell1 = resultRow6.createCell(9);
			 retScroeCell1.setCellStyle(rlLineCCellStyle);
			
			 Cell retScroeCell2 = resultRow7.createCell(9);
			 retScroeCell2.setCellStyle(rlLineCCellStyle);
			 
			
			
			//添加评阅内容
			sheet.addMergedRegion(new CellRangeAddress(7, 41, 0, 9));
			 Cell retScroeCell3 = resultRow6.createCell(9);
			 retScroeCell3.setCellStyle(rlLineCCellStyle);
			HSSFRow adviceRow = sheet.createRow(7);
			Cell adviceCell = adviceRow.createCell(0);
			adviceCell.setCellStyle(upLLineLCellStyle);
			adviceCell.setCellValue("评阅意见："+reviewProject.getRemark());
			//第一行加边框
			
			for(int i = 1;i<=8;i++){
				Cell adviceCell1 = adviceRow.createCell(i);
				adviceCell1.setCellStyle(upLineLCellStyle);
			}
			Cell adviceCell2 = adviceRow.createCell(9);
			adviceCell2.setCellStyle(upRLineLCellStyle);
			
			
			
			for(int i = 8;i<=40;i++){
				HSSFRow row = sheet.createRow(i);
				Cell leftCell = row.createCell(0);
				Cell rightCell = row.createCell(9);
				rightCell.setCellStyle(rightLLineLCellStyle);
				leftCell.setCellStyle(leftLLineLCellStyle);
			}
			
		    HSSFRow row1 = sheet.createRow(41);
		    for(int i = 1; i<9;i++){
		    	Cell downCell = row1.createCell(i);
		    	downCell.setCellStyle(downLLineLCellStyle);
		    }
			
		    Cell downCell1 = row1.createCell(0);
		    downCell1.setCellStyle(downLLineLCellStyle);
		    
		    Cell downCell2 = row1.createCell(9);
		    downCell2.setCellStyle(downRLineLCellStyle);
			//评阅人签名
			HSSFRow assignNameRow = sheet.createRow(42);
			Cell assignNameCell = assignNameRow.createCell(5);
			assignNameCell.setCellValue("评阅人签名:");
			
			//日期
			
			HSSFRow dateRow = sheet.createRow(44);
			sheet.addMergedRegion(new CellRangeAddress(44,44,6,8));
			Cell dateCell = dateRow.createCell(6);
			dateCell.setCellValue(formattedDate);
			dateCell.setCellStyle(dateToRightCellStyle);
			
			//表的出处以及注
			HSSFRow tableFromRow = sheet.createRow(47);
			Cell tableFromCell = tableFromRow.createCell(9);
			tableFromCell.setCellValue("教务处制");
			
			sheet.addMergedRegion(new CellRangeAddress(48, 48, 0, 9));
			HSSFRow noticeRow = sheet.createRow(48);
			Cell noticeCell = noticeRow.createCell(0);
			noticeCell.setCellValue("注：各专业应根据自己的具体情况，制定出适合本专业的毕业设计（论文）的具体要求和评分标准。");
			
			
		
		
	}
	
	
	/**
	 * 设置列宽
	 * @param sheet 工作表
	 * @param fontSize 宽度
	 */
	private void setColumns(HSSFSheet sheet, int columnWidth) {
		sheet.setColumnWidth(0, 2000);//第一列的宽度；
		sheet.setColumnWidth(1, columnWidth);//第二列的宽度；
		sheet.setColumnWidth(2, columnWidth);//第二列的宽度；
	}
}
